using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using Sinacor.Infra.Service.Data;
using Sinacor.Infra.Service.Security.Authorization;

namespace Sinacor.Servico.Bovespa.Contabil.ResourceAccess
{
	public class DespesaNota : BaseResourceAccess 
    {
		public DespesaNota(): base(AuthorizationContext.Current.CompanyId) {} 

		public void AlterarDespesaNota(BusinessEntity.DespesaNota entity)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("UPDATE TBODESPNO SET DT_PREFIM = :PregaoFinal, ");
            sql.Append(" PC_CUSTO = :PercentualDespesa, VL_MIN_COBR = :ValorMinimo, ");
            sql.Append(" VL_MAX_COBR = :ValorMaximo, IN_CORRETAGEM = :BaseDespesa, ");
            sql.Append(" IN_IRRF = :IncideIrrf, CD_EVECON = :EventoContabil ");
            sql.Append(" WHERE CD_DESP_NOTA = :Codigo ");
            sql.Append(" AND DT_PREINI = :PregaoInicial");

            string _tipoBaseDespesa = Sinacor.Infra.Service.Common.EnumConverter.ToString<BusinessEntity.Enum.TipoBaseDespesa>((BusinessEntity.Enum.TipoBaseDespesa)entity.BaseDespesa);

            using (DbCommand dbCommand = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {
                this.DataBase.AddInParameter(dbCommand, ":PregaoFinal", DbType.DateTime, entity.PregaoFinal);
                this.DataBase.AddInParameter(dbCommand, ":PercentualDespesa", DbType.Decimal, entity.PercentualDespesa);
                this.DataBase.AddInParameter(dbCommand, ":ValorMinimo", DbType.Decimal, entity.ValorMinimo);
                this.DataBase.AddInParameter(dbCommand, ":ValorMaximo", DbType.Decimal, entity.ValorMaximo);

                this.DataBase.AddInParameter(dbCommand, ":BaseDespesa", DbType.String, _tipoBaseDespesa);
                this.DataBase.AddInParameter(dbCommand, ":IncideIrrf", DbType.String, entity.IncideIrrf ? "S" : "N");
                this.DataBase.AddInParameter(dbCommand, ":EventoContabil", DbType.String, entity.EventoContabil.Codigo);
                this.DataBase.AddInParameter(dbCommand, ":Codigo", DbType.String, entity.Codigo);
                this.DataBase.AddInParameter(dbCommand, ":PregaoInicial", DbType.DateTime, entity.PregaoInicial);

                this.DataBase.ExecuteNonQuery(dbCommand);
            }
		}

		public List<BusinessEntity.DespesaNota> ConsultarConflitoPeriodo(BusinessEntity.DespesaNota entity)
        {
            List<BusinessEntity.DespesaNota> response = null;
            StringBuilder sql = new StringBuilder();

            sql.Append(" SELECT CD_DESP_NOTA Codigo, DT_PREINI PregaoInicial, DT_PREFIM PregaoFinal, ");
		    sql.Append(" PC_CUSTO PercentualDespesa, VL_MIN_COBR ValorMinimo, VL_MAX_COBR ValorMaximo, ");
		    sql.Append(" IN_CORRETAGEM SobreDesconto, IN_IRRF IncideIrrf, CD_EVECON EventoContabil FROM TBODESPNO ");
            sql.Append(" WHERE CD_DESP_NOTA = :Codigo AND DT_PREINI <> :PregaoInicial ");
            sql.Append(" AND(((:PregaoInicial BETWEEN DT_PREINI AND DT_PREFIM) ");
		    sql.Append(" OR (:PregaoFinal BETWEEN DT_PREINI AND DT_PREFIM)) ");
		    sql.Append(" OR ((DT_PREINI BETWEEN :PregaoInicial AND :PregaoFinal) ");
		    sql.Append(" OR (DT_PREFIM BETWEEN :PregaoInicial AND :PregaoFinal))) ");
		    sql.Append(" ORDER BY DT_PREINI ");

            using (DbCommand dbCommand = this.DataBase.GetSqlStringCommand(sql.ToString() ))
            {
                this.DataBase.AddInParameter(dbCommand, ":Codigo", DbType.String, entity.Codigo);
                this.DataBase.AddInParameter(dbCommand, ":PregaoInicial", DbType.DateTime, entity.PregaoInicial);
                this.DataBase.AddInParameter(dbCommand, ":PregaoFinal", DbType.DateTime, entity.PregaoFinal);

                response = this.FindAll<BusinessEntity.DespesaNota>(dbCommand,
                    reader =>
                    {
                        BusinessEntity.DespesaNota despesaNota = new BusinessEntity.DespesaNota();

                        despesaNota.Codigo = reader.GetValue<String>("Codigo");
                        despesaNota.PregaoInicial = reader.GetValue<DateTime>("PregaoInicial");
                        despesaNota.PregaoFinal = reader.GetValue<DateTime>("PregaoFinal");
                        despesaNota.PercentualDespesa = reader.GetValue<Decimal>("PercentualDespesa");
                        despesaNota.ValorMinimo = reader.GetValue<Decimal>("ValorMinimo");
                        despesaNota.ValorMaximo = reader.GetValue<Decimal>("ValorMaximo");
                        despesaNota.BaseDespesa = Sinacor.Infra.Service.Common.EnumConverter.Parse<BusinessEntity.Enum.TipoBaseDespesa>(reader.GetValue<String>("SobreDesconto"));
                        despesaNota.IncideIrrf = (reader.GetValue<string>("IncideIrrf") == "S");

                        despesaNota.EventoContabil = new BusinessEntity.EventoContabil();
                        despesaNota.EventoContabil.Codigo = reader.GetValue<Int32>("EventoContabil");

                        return despesaNota;
                    });
                return response;
            }
		}

		public List<BusinessEntity.DespesaNota> ConsultarDespesasNota(BusinessEntity.Parameter.FiltroDespesaNota parameter)
        {
            List<BusinessEntity.DespesaNota> response = new List<Sinacor.Servico.Bovespa.Contabil.BusinessEntity.DespesaNota>();
            StringBuilder sql = new StringBuilder();

            sql.Append(" SELECT CD_DESP_NOTA Codigo, DT_PREINI PregaoInicial, DT_PREFIM PregaoFinal, PC_CUSTO PercentualDespesa, ");
            sql.Append(" VL_MIN_COBR ValorMinimo, VL_MAX_COBR ValorMaximo, IN_CORRETAGEM SobreDesconto, IN_IRRF IncideIrrf, ");
		    sql.Append(" TBODESPNO.CD_EVECON EventoContabil, DS_EVECON DescricaoEventoContabil FROM TBODESPNO INNER JOIN TBOEVECON ON TBOEVECON.CD_EVECON = TBODESPNO.CD_EVECON WHERE 1=1 ");
            
            if ((parameter.Codigo != null) && (parameter.Codigo != string.Empty))
            {
                sql.Append(" AND CD_DESP_NOTA = :Codigo ");
            }

            if (parameter.PregaoInicial != null)
            {
                sql.Append(" AND DT_PREINI >= :PregaoInicial ");
            }
		    
            if (parameter.PregaoFinal != null)
            {
                sql.Append(" AND DT_PREFIM <= :PregaoFinal ");
            }

            if (parameter.PercentualDespesa != null)
            {
                sql.Append(" AND PC_CUSTO = :PercentualDespesa ");
            }

            if (parameter.ValorMinimo != null)
            {
                sql.Append(" AND VL_MIN_COBR = :ValorMinimo ");
            }
		 
            if (parameter.ValorMaximo != null)
            {
                sql.Append(" AND VL_MAX_COBR = :ValorMaximo ");
            }

            if (parameter.BaseDespesa != null)
            {
                sql.Append(" AND IN_CORRETAGEM = :BaseDespesa ");
            }
		 
            if (parameter.IncideIrrf != null)
            {
                sql.Append(" AND IN_IRRF = :IncideIrrf ");
            }
	 
            if (parameter.EventoContabil != null)
            {
                sql.Append(" AND TBODESPNO.CD_EVECON = :EventoContabil ");
            }

            sql.Append(" ORDER BY DT_PREINI ");

            using (DbCommand dbCommand = this.DataBase.GetSqlStringCommand(sql.ToString() ))
            {
                if ((parameter.Codigo != null) && (parameter.Codigo != string.Empty))
                {
                    this.DataBase.AddInParameter(dbCommand, ":Codigo", DbType.String, parameter.Codigo);
                }

                if (parameter.PregaoInicial != null)
                {
                    this.DataBase.AddInParameter(dbCommand, ":PregaoInicial", DbType.DateTime, parameter.PregaoInicial);
                }

                if (parameter.PregaoFinal != null)
                {
                    this.DataBase.AddInParameter(dbCommand, ":PregaoFinal", DbType.DateTime, parameter.PregaoFinal);
                }

                if (parameter.PercentualDespesa != null)
                {
                    this.DataBase.AddInParameter(dbCommand, ":PercentualDespesa", DbType.Decimal, parameter.PercentualDespesa);
                }

                if (parameter.ValorMinimo != null)
                {
                    this.DataBase.AddInParameter(dbCommand, ":ValorMinimo", DbType.Decimal, parameter.ValorMinimo);
                }

                if (parameter.ValorMaximo != null)
                {
                    this.DataBase.AddInParameter(dbCommand, ":ValorMaximo", DbType.Decimal, parameter.ValorMaximo);
                }

                if (parameter.BaseDespesa != null)
                {
                    this.DataBase.AddInParameter(dbCommand, ":BaseDespesa", DbType.String, parameter.BaseDespesa);
                }

                if (parameter.IncideIrrf != null)
                {
                    this.DataBase.AddInParameter(dbCommand, ":IncideIrrf", DbType.Boolean, parameter.IncideIrrf);
                }

                if (parameter.EventoContabil != null)
                {
                    this.DataBase.AddInParameter(dbCommand, ":EventoContabil", DbType.String, parameter.EventoContabil);
                }

                response = this.FindAll<BusinessEntity.DespesaNota>(dbCommand,
                    reader =>
                    {
                        BusinessEntity.DespesaNota despesaNota = new BusinessEntity.DespesaNota();

                        despesaNota.Codigo = reader.GetValue<String>("Codigo");
                        despesaNota.PregaoInicial = reader.GetValue<DateTime>("PregaoInicial");
                        despesaNota.PregaoFinal = reader.GetValue<DateTime>("PregaoFinal");
                        despesaNota.PercentualDespesa = reader.GetValue<Decimal>("PercentualDespesa");
                        despesaNota.ValorMinimo = reader.GetValue<Decimal>("ValorMinimo");
                        despesaNota.ValorMaximo = reader.GetValue<Decimal>("ValorMaximo");
                        despesaNota.BaseDespesa = Sinacor.Infra.Service.Common.EnumConverter.Parse<BusinessEntity.Enum.TipoBaseDespesa>(reader.GetValue<String>("SobreDesconto"));
                        despesaNota.IncideIrrf = (reader.GetValue<string>("IncideIrrf") == "S");

                        despesaNota.EventoContabil = new BusinessEntity.EventoContabil();
                        despesaNota.EventoContabil.Codigo = reader.GetValue<Int32>("EventoContabil");
                        despesaNota.EventoContabil.Descricao = reader.GetValue<String>("DescricaoEventoContabil");

                        return despesaNota;
                    });
                return response;
            }
        }

		public void ExcluirDespesaNota(BusinessEntity.Parameter.FiltroDespesaNotaChave parameter)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("DELETE FROM TBODESPNO WHERE CD_DESP_NOTA = :Codigo AND DT_PREINI = :PregaoInicial");

            using (DbCommand dbCommand = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {
                this.DataBase.AddInParameter(dbCommand, ":Codigo", DbType.String, parameter.Codigo);
                this.DataBase.AddInParameter(dbCommand, ":PregaoInicial", DbType.DateTime, parameter.PregaoInicial);

                this.DataBase.ExecuteNonQuery(dbCommand);
            }
		}

		public void IncluirDespesaNota(BusinessEntity.DespesaNota entity)
        {
		    List<BusinessEntity.DespesaNota> response = null;
            StringBuilder sql = new StringBuilder();

            sql.Append(" INSERT INTO TBODESPNO (CD_DESP_NOTA, DT_PREINI, DT_PREFIM, PC_CUSTO, ");
            sql.Append(" VL_MIN_COBR, VL_MAX_COBR, IN_CORRETAGEM, IN_IRRF, CD_EVECON) ");
            sql.Append(" VALUES (:Codigo, :PregaoInicial, :PregaoFinal, :PercentualDespesa, ");
            sql.Append(" :ValorMinimo, :ValorMaximo, :BaseDespesa, :IncideIrrf, :EventoContabil) ");

            string _tipoBaseDespesa = Sinacor.Infra.Service.Common.EnumConverter.ToString<BusinessEntity.Enum.TipoBaseDespesa>((BusinessEntity.Enum.TipoBaseDespesa)entity.BaseDespesa);
                             
            using (DbCommand dbCommand = this.DataBase.GetSqlStringCommand(sql.ToString() ))
            {
                this.DataBase.AddInParameter(dbCommand, ":Codigo", DbType.String, entity.Codigo);
                this.DataBase.AddInParameter(dbCommand, ":PregaoInicial", DbType.DateTime, entity.PregaoInicial);
                this.DataBase.AddInParameter(dbCommand, ":PregaoFinal", DbType.DateTime, entity.PregaoFinal);
                this.DataBase.AddInParameter(dbCommand, ":PercentualDespesa", DbType.Decimal, entity.PercentualDespesa);
                this.DataBase.AddInParameter(dbCommand, ":ValorMinimo", DbType.Decimal, entity.ValorMinimo);
                this.DataBase.AddInParameter(dbCommand, ":ValorMaximo", DbType.Decimal, entity.ValorMaximo);
                this.DataBase.AddInParameter(dbCommand, ":BaseDespesa", DbType.String, _tipoBaseDespesa);
                this.DataBase.AddInParameter(dbCommand, ":IncideIrrf", DbType.String, entity.IncideIrrf ? "S" : "N");
                this.DataBase.AddInParameter(dbCommand, ":EventoContabil", DbType.String, entity.EventoContabil.Codigo);

                this.DataBase.ExecuteNonQuery(dbCommand);
            }	 
		}

        public bool ValidarExistenciaDespesaNota(BusinessEntity.Parameter.FiltroDespesaNotaChave request)
        {
            object response = null;
            StringBuilder sql = new StringBuilder();

            sql.Append(" SELECT 0 ");
            sql.Append(" FROM TBODESPNO ");
            sql.Append(" INNER JOIN TBOEVECON ON TBOEVECON.CD_EVECON = TBODESPNO.CD_EVECON  ");
            sql.Append(" WHERE CD_DESP_NOTA = :Codigo AND DT_PREINI = :PregaoInicial ");

            using (DbCommand dbCommand = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {
                this.DataBase.AddInParameter(dbCommand, ":Codigo", DbType.String, request.Codigo);
                this.DataBase.AddInParameter(dbCommand, ":PregaoInicial", DbType.DateTime, request.PregaoInicial);

                response = this.DataBase.ExecuteScalar(dbCommand);
            }

            return (response != null);
        }
	}
}